from flask import (Blueprint, request, session)

from flaskr.server.FilterDataServer import *
from flaskr.server.FilterRecord import *

import pandas as pd
bp = Blueprint('filter', __name__, url_prefix='/filter')

'''
获取该表的过滤容器中的对象
sheet_name 过滤对象的加载表的表名
return DataFrame对象 或者 一个object对象
'''
# def get_filter_container(sheet_name):
#     filter_container = None
#
#     if 'filterContainer' not in session.keys():
#         session['filterContainer'] = {}
#         filter_container = session['filterContainer']
#
#     else:
#         filter_container = session['filterContainer']
#
#     if sheet_name is None:
#         return filter_container
#
#     if sheet_name not in filter_container.keys():
#         filter_container[sheet_name] = {} #这里面存放的是过滤容器
#         df = getLoadSheet(sheet_name)
#         filter_container[sheet_name] = Utils.dataFrameToJson(df)
#         session['filterContainer'] = filter_container
#         return df
#
#     else:
#         df = Utils.jsonToDataFrame(filter_container[sheet_name])
#         return df

filterDataServer = FilterDataServer(session)
filter_record = FilterRecord()

@bp.route('', methods = ['POST'])
def filter():
    sheet_name = request.json.get('sheet_name')
    condition = request.json.get('condition')

    df = filterDataServer.get_filter_container(sheet_name)

    df = df.query(condition)

    filterDataServer.set_filter_container(sheet_name, df)

    json_ = filterDataServer.data_frame_to_filter_json(df, sheet_name, '数据过滤')

    return{
        'data': json_,
        'code': 200,
        'msg': '过滤成功',
    }

#数据过滤，获取需要过滤的数据，并且返回给客户端
@bp.route('/old', methods = ['POST'])
def filterData():
    columns_filter = request.json.get('columns')#获取过滤条件
    sheet_name = request.json.get('sheet_name') #获取数据表名

    df = filterDataServer.get_filter_container(sheet_name)

    result = filterDataServer.parse(columns_filter)

    if result != None:
        df = df.query(result) #调用DataFrame的过滤函数

        print(df)

        json_ = Utils.dataFrameToJson(df) #将过滤了的数据转换成json
        html = Utils.dataFrameToHtml(df) #将过滤了的数据转换成html
        types = Utils.getTypes(df) #获取类型
        return{
            'code': 200,
            'msg': '过滤成功',
            'data': html,
            'types': types,
        }

    else:
        html = Utils.dataFrameToHtml(df)

        return{
            'code': 500,
            'msg' : '过滤失败',
            'data': html
        }

@bp.route('/setType', methods = ['POST'])
def set_type():
    sheet_name = request.json.get('sheetName')
    field_name = request.json.get('field')
    type = request.json.get('type')

    df = filterDataServer.get_filter_container(sheet_name)


    if type == 'datetime':
        df['day_id'] = df['day_id'].astype('object')
        df[field_name] = pd.to_datetime(df[field_name])

    elif type == 'object':
        df[field_name] = df[field_name].astype('category')

    elif type == 'float':
        df[field_name] = df[field_name].astype('float')

    elif type == 'int':
        df[field_name] = df[field_name].astype('int')

    filterDataServer.set_filter_container(sheet_name, df)

    json_ = filterDataServer.data_frame_to_filter_json(df, sheet_name, '改变类型')

    return{
        'data': json_,
        'code': 200,
        'msg': '修改成功',
    }

'''
选择对应的字段名，而不是导入数据中的所有字段名
'''
@bp.route('/column', methods = ['POST'])
def choose_column():
    sheet_name = request.json.get('sheet_name')
    columns = request.json.get('columns')
    df = filterDataServer.get_filter_container(sheet_name)

    df = df.loc[:, columns]

    filterDataServer.set_filter_container(sheet_name, df)

    json_ = filterDataServer.data_frame_to_filter_json(df, sheet_name, '删除字段')

    return{
        'data': json_,
        'code': 200,
        'msg': '过滤成功',
    }

'''
删除重复项
'''
@bp.route('/delRepeat', methods = ['POST'])
def del_repeat():
    sheet_name = request.json.get('sheet_name')
    columns = request.json.get('columns')
    df = filterDataServer.get_filter_container(sheet_name)
    df = df.drop_duplicates(columns)

    filterDataServer.set_filter_container(sheet_name, df)

    json_ = filterDataServer.data_frame_to_filter_json(df, sheet_name, '删除重复项')

    return{
        'data': json_,
        'code': 200,
        'msg': '过滤成功'
    }

'''
删除前几行数据
'''
@bp.route('/delFrontLine', methods = ['POST'])
def del_front_line():
    number_line = request.json.get('numberLine')
    sheet_name = request.json.get('sheet_name')

    df = filterDataServer.get_filter_container(sheet_name)

    df = df.drop(index=df.head(number_line).index)

    filterDataServer.set_filter_container(sheet_name, df)

    json_ = filterDataServer.data_frame_to_filter_json(df, sheet_name, '删除前面几行')

    return{
        'data': json_,
        'code': 200,
        'msg': '过滤成功'
    }

'''
删除最后几行
'''
@bp.route('/delLastLine', methods = ['POST'])
def del_last_line():
    number_line = request.json.get('numberLine')
    sheet_name = request.json.get('sheet_name')

    df = filterDataServer.get_filter_container(sheet_name)

    df = df.drop(index=df.tail(number_line).index)

    filterDataServer.set_filter_container(sheet_name, df)

    json_ = filterDataServer.data_frame_to_filter_json(df, sheet_name, '删除最后几行')

    return{
        'data': json_,
        'code': 200,
        'msg': '过滤成功'
    }

'''
删除空白项
'''
@bp.route('/delBlank', methods = ['POST'])
def del_blank():
    sheet_name = request.json.get('sheet_name')
    column = request.json.get('column')

    df = filterDataServer.get_filter_container(sheet_name)
    index = df[df[column].isnull()].index
    df = df.drop(index=index)
    filterDataServer.set_filter_container(sheet_name, df)

    json_ = filterDataServer.data_frame_to_filter_json(df, sheet_name, '删除空白项')

    return{
        'data': json_,
        'code': 200,
        'msg': '过滤成功'
    }

'''
获取加载表中相应的过滤容器对象的数据
'''
@bp.route('/getContainer', methods = ['POST'])
def get_container():
    sheet_name = request.json.get('sheet_name')
    df = filterDataServer.get_filter_container(sheet_name)

    if df is None:
        return{
            'code': 500,
            'data': [],
            'msg': '容器为空'
        }

    json_ = filterDataServer.data_frame_to_filter_json(df, sheet_name, None)

    return{
        'data': json_,
        'code': 200,
    }


'''
将加载表的数据替换成对应的过滤数据
'''
@bp.route('/save', methods = ['POST'])
def save():
    sheet_name = request.json.get('sheet_name') #获取数据表名

    result = filterDataServer.save_filter(sheet_name)

    # sheet = get_sheet(sheet_name)
    df = filterDataServer.get_filter_container(sheet_name)
    filter_record.clearRecord(sheet_name)
    json_ = filterDataServer.data_frame_to_filter_json(df, sheet_name, None)
    if result:

        return{
            'code': 200,
            'data': json_,
            'msg': '保存成功',
        }

    else:
        return {
        'code': 500,
        'msg': '保存失败'
    }


#解析客户端传送的条件请求
def parse(data):
    result = ""

    for column in data:
        string = ""
        column_name = column['column']

        for i in range(len(column['value'])):
            ro = roParse(column['ro'][i])

            if ro is None:
                return None

            if(column['type'] != 'object'):
                string += column_name + ro + str(column['value'][i]) #第i个条件

            else:
                string += column_name + ro + '"' + str(column['value'][i]) + '"'

            if(i < len(column['logic'])):
                string += ' ' + column['logic'][i] + ' ' # 条件的逻辑运算符


        result += '(' + string + ')' + ' and '

    # 将多余的and删除
    index = result.rfind(' and')
    result = result[0:index]

    return result

#关系运算符解析器
def roParse(ro):
    if ro == 'lt':
        return '<'

    elif ro == 'gt':
        return '>'

    elif ro == 'et':
        return '=='

    elif ro == 'ut':
        return '!='

    elif ro == 'get':
        return '>='

    elif ro == 'let':
        return '<='

    else:
        return None

'''
获取加载表中的DataFrame对象

sheet_name 加载表的名字

return DataFrame对象
'''
def getLoadSheet(sheet_name):
    sheets = []
    json_ = None
    if('sheets' in session.keys()):
        sheets = session['sheets']

    for sheet in sheets:
        if(sheet['showName'] == sheet_name):
            json_ = sheet['json'] #通过数据名称和数据表名从session中拿到对应的json数据
            break

    if json_ == None:
        return{
            'msg': '过滤失败,未找到该表格',
            'data': 'null'
        }

    df = Utils.jsonToDataFrame(json_) #将json数据转换成df

    return df


'''
将dataFrame对象转换成前端对应的json格式

data_frame pandas的DataFrame对象

return DataFrame对象的json
'''
def data_frame_to_filter_json(data_frame, name):
    data = {}
    table = Utils.dataFrameToHtml(data_frame)
    columns = Utils.getColumns(data_frame)
    data['table'] = table

    types = Utils.getTypes(data_frame)
    data['types'] = types

    if name is not None:
        data['name'] = name

    data['data'] = {}
    for i in range(len(types)):
        obj = {}
        values = list(data_frame[columns[i]].values)

        for j in range(len(values)):
            values[j] = str(values[j])

        # obj['values'] = json.dumps(values)

        if types[i] == 'int64' or types[i] == 'float64':
            obj['filter'] = ['等于', '不等于', '大于', '大于或等于', '小于', '小于或等于']
            obj['symbol'] = ['et', 'ut', 'gt', 'get', 'lt', 'let']

        else:
            obj['filter'] = ['等于', '不等于']
            obj['symbol'] = ['et', 'ut']

        data['data'][columns[i]] = obj


    return json.dumps(data)

'''
将数据设置为指定的过滤容器数据
sheet_name 加载表的表名
df 指定的数据
'''
def set_filter_container(sheet_name, df):

    df_json = Utils.dataFrameToJson(df)

    if 'filterContainer' not in session.keys():
        session['filterContainer'] = {}

    filter_container = session['filterContainer']

    filter_container[sheet_name] = df_json



@bp.route('/resetFilter', methods=['POST'])
def reset_filter():
    sheet_name = request.json.get('sheet_name')

    filterDataServer.remove_filter_container(sheet_name)

    df = filterDataServer.get_filter_container(sheet_name)
    filter_record.clearRecord(sheet_name)
    json_ = filterDataServer.data_frame_to_filter_json(df, sheet_name, None)

    return{
        'code': 200,
        'data': json_,
        'msg': '重置成功'
    }




